How to Create an Excel : Complete Guide to Equal Principal and Interest Payments and Equal Principal Payments

Want to see your loan payments clearly? Build a complete schedule in minutes

Creating a loan amortization schedule helps you visualize your monthly payments, interest, and remaining balance over the life of your loan. Whether you're borrowing for a home, car, or personal loan, Excel functions like PMT, IPMT, and PPMT can turn complex math into automated clarity. In this guide, I'll walk you through step-by-step how to set up a smart, dynamic schedule—based on real examples, personal observations, and practical templates.

Understanding Loan Repayment Types Before You Start

Before you even open Excel, know this: your entire schedule depends on the repayment type you choose. There are two main types—

Equal principal and interest  means you pay the same amount every month. The interest portion is high in the beginning and gradually reduces as the principal component increases.

Equal principal repayment  keeps your principal payment the same each month, while interest decreases over time. It's more expensive upfront but saves you money long term.

For example, borrowing 100 million KRW at 4% interest over 3 years results in total interest of about 6.28 million KRW for equal payments, compared to 6.16 million KRW for equal principal.

Step-by-Step: Building the Structure in Excel

Let’s lay the foundation. First, define an input zone for key variables like loan amount, interest rate, and term. Here’s what that typically looks like:

Input	Example
Annual Interest Rate	4%
Loan Term (Years)	3
Payments per Year	12
Loan Amount	100,000,000

Next, add headers: Installment No, Payment, Interest, Principal, and Remaining Balance. Start numbering from 1 to the total number of periods (e.g., 36 months).

Equal Payment Schedule Using PMT Function

In equal payment loans, you pay the same amount monthly. Here's how to automate it using Excel's PMT function.

Use this formula to calculate monthly payments (assuming C2 = annual interest rate, C3 = years, C5 = amount): =PMT(C2/12, C3*12, -C5)

Then, for each row:

• B column (Payment): = $B$4
• C column (Interest): = E7*(C2/12)
• D column (Principal): = B8 - C8
• E column (Remaining): = E7 - D8

Drag down to complete all 36 periods. Total repayment and interest can be summed easily using SUM().

Advanced: IPMT and PPMT Functions for Breakdown

For more precise calculations, use Excel’s financial functions:

Function	Purpose
IPMT	Interest for each period
PPMT	Principal for each period

Example for Row 8:

• C8: =IPMT($C$2/12, A8, $C$3*12, -$C$5)
• D8: =PPMT($C$2/12, A8, $C$3*12, -$C$5)
• B8: =C8 + D8

Drag the formula down. It’ll dynamically adjust the interest and principal portions.

Equal Principal Repayment: A Different Structure

In equal principal loans, your principal stays constant. Interest drops as balance declines.

Use this formula:

• Principal: = $C$5 / ($C$3*12)
• Interest: = E7 * ($C$2/12)
• Payment: = Principal + Interest
• Remaining Balance: = E7 - Principal

Initial payments will be higher, but your interest outflow reduces each month.

Adding Extra Payments or Variable Rates

Want to pay faster or handle a changing interest rate? Add two new columns:

1. Extra Payment (F column): Subtract from remaining balance. E8 = E7 - D8 - F8
2. Variable Interest (G column): Replace fixed rate in interest calc. C8 = E7*(G8/12)

This customization can be a lifesaver when your loan terms change mid-way.

Use Templates If You're In a Hurry

Don’t want to build it from scratch? Grab an Excel loan template or use online calculators from banks like Woori. Just plug in your values and go.

Tool	What It Offers	Best For
Loan Template (Excel)	Editable amortization sheet	DIY customization
Bank Calculators	Compare repayment types	Quick comparisons

Some tools even let you model long-term loans up to 30 years, with auto-updating charts.

Upbit Staking Guide 2026: Profit Estimator, APY by Coin, Real Earnings Explained

#loanamortization #excelpaymentschedule #loanrepaymentplanner #financialtools #monthlyrepaymentcalculator #excelfunctions loan amortization